﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace SoftwareEngineering_Project
{
    public class DBConnection
    {
        public static string cnnStr;
        private string myconString;
        public SqlConnection cnTransact;
        SqlDataReader myReader = null;

        //To create the DataBase connection and open it
        public DBConnection()
        {
            myconString = "Data Source=rkuwap9d9t.database.windows.net;Initial Catalog=s-cheduleDB;Persist Security Info=True;User ID=cactus;Password=SunDancer81";
            cnTransact = new SqlConnection(myconString);
        }

        
        //Execute SQl command with no returned values like Insert, or Delete
        public Boolean ExecuteCommandNonquery(string mySqlCommand)
        {
            if (cnTransact.State != System.Data.ConnectionState.Open)
            {
                cnTransact = new SqlConnection(myconString);
                cnTransact.Open();
            }          
            SqlCommand myCommand = new SqlCommand(mySqlCommand, cnTransact);
            try
            {
                myCommand.ExecuteNonQuery();
                //cnTransact.Close();
            }
            catch (Exception err)
            {
                return false;
            }
            return true;
        }
        public SqlDataReader GetSqlReader()
        {
            return myReader;
        }
        //Execute SQl command with  returned values like Select from
        public SqlDataReader ExecuteCommandquery(string mySqlCommand)
        {

            if (cnTransact.State != System.Data.ConnectionState.Open)
            {
                cnTransact = new SqlConnection(myconString);
                cnTransact.Open();
            }
            SqlCommand myCommand = new SqlCommand(mySqlCommand, cnTransact);
            
            try
            {
                myReader = myCommand.ExecuteReader();
                //cnTransact.Close();
            }
            catch (Exception err )
            {
                return null ;
            }
            return myReader;
        }
        public Int32 GetlastID(string mysqlCommand)
        {
            Int32 id = 0;
            if (cnTransact.State != System.Data.ConnectionState.Open)
            {
                cnTransact = new SqlConnection(myconString);
                cnTransact.Open();
            }
            SqlCommand myCommand = new SqlCommand(mysqlCommand, cnTransact);
            try
            {
                id = Convert.ToInt32(myCommand.ExecuteScalar());
                //cnTransact.Close();
            }
            catch (Exception )
            {
                return 0;
            }
            return id;
        }

    }
}